CREATE OR REPLACE FUNCTION sp_quotation_item_insert(
	_id_quotation	integer,
	_id_parent	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_description	character varying DEFAULT null,
	_remark	character varying DEFAULT null,
	_is_group	boolean DEFAULT null,
	_index	integer DEFAULT null,
	_display_index	character varying(10) DEFAULT null,
	_id_product	integer DEFAULT null,
	_id_service	integer DEFAULT null,
	_id_unit	integer DEFAULT null,
	_id_currency	integer DEFAULT null,
	_estimate_quantity	integer DEFAULT null,
	_quotation_quantity	integer DEFAULT null,
	_internal_quantity	integer DEFAULT null,
	_estimate_unit_cost	numeric DEFAULT null,
	_quotation_unit_cost	numeric DEFAULT null,
	_internal_unit_cost	numeric DEFAULT null,
	_estimate_total_amount	numeric DEFAULT null,
	_quotation_total_amount	numeric DEFAULT null,
	_internal_total_amount	numeric DEFAULT null,
	_in_estimate	boolean DEFAULT null,
	_in_quotation	boolean DEFAULT null,
	_in_internal	boolean DEFAULT null,
	_id_type	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO quotation_item(id_quotation,id_parent,code,name,description,remark,is_group,index,display_index,id_product,id_service,id_unit,id_currency,estimate_quantity,quotation_quantity,internal_quantity,estimate_unit_cost,quotation_unit_cost,internal_unit_cost,estimate_total_amount,quotation_total_amount,internal_total_amount,in_estimate,in_quotation,in_internal,id_type,status,notification,creator,updator,deletion_time)
	VALUES(_id_quotation,_id_parent,_code,_name,_description,_remark,_is_group,_index,_display_index,_id_product,_id_service,_id_unit,_id_currency,_estimate_quantity,_quotation_quantity,_internal_quantity,_estimate_unit_cost,_quotation_unit_cost,_internal_unit_cost,_estimate_total_amount,_quotation_total_amount,_internal_total_amount,_in_estimate,_in_quotation,_in_internal,_id_type,_status,_notification,_creator,_creator,null);
RETURN currval('quotation_item_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_quotation_item_select(_id	integer)
RETURNS SETOF quotation_item
AS $$
BEGIN
	RETURN QUERY SELECT * FROM quotation_item WHERE id = _id;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_quotation_item_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_parent	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_description	character varying DEFAULT null,
	_remark	character varying DEFAULT null,
	_is_group	boolean DEFAULT null,
	_index	integer DEFAULT null,
	_display_index	character varying(10) DEFAULT null,
	_id_product	integer DEFAULT null,
	_id_service	integer DEFAULT null,
	_id_unit	integer DEFAULT null,
	_id_currency	integer DEFAULT null,
	_estimate_quantity	integer DEFAULT null,
	_quotation_quantity	integer DEFAULT null,
	_internal_quantity	integer DEFAULT null,
	_estimate_unit_cost	numeric DEFAULT null,
	_quotation_unit_cost	numeric DEFAULT null,
	_internal_unit_cost	numeric DEFAULT null,
	_estimate_total_amount	numeric DEFAULT null,
	_quotation_total_amount	numeric DEFAULT null,
	_internal_total_amount	numeric DEFAULT null,
	_in_estimate	boolean DEFAULT null,
	_in_quotation	boolean DEFAULT null,
	_in_internal	boolean DEFAULT null,
	_id_type	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS SETOF quotation_item
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM quotation_item WHERE
	 	(_id is null OR id = _id)	
	AND (_id_quotation is null OR id_quotation = _id_quotation)	
	AND (_id_parent is null OR id_parent = _id_parent)	
	AND (_code is null OR code = _code)	
	AND (_name is null OR name = _name)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_is_group is null OR is_group = _is_group)	
	AND (_index is null OR index = _index)	
	AND (_display_index is null OR display_index = _display_index)	
	AND (_id_product is null OR id_product = _id_product)	
	AND (_id_service is null OR id_service = _id_service)	
	AND (_id_unit is null OR id_unit = _id_unit)	
	AND (_id_currency is null OR id_currency = _id_currency)	
	AND (_estimate_quantity is null OR estimate_quantity = _estimate_quantity)	
	AND (_quotation_quantity is null OR quotation_quantity = _quotation_quantity)	
	AND (_internal_quantity is null OR internal_quantity = _internal_quantity)	
	AND (_estimate_unit_cost is null OR estimate_unit_cost = _estimate_unit_cost)	
	AND (_quotation_unit_cost is null OR quotation_unit_cost = _quotation_unit_cost)	
	AND (_internal_unit_cost is null OR internal_unit_cost = _internal_unit_cost)	
	AND (_estimate_total_amount is null OR estimate_total_amount = _estimate_total_amount)	
	AND (_quotation_total_amount is null OR quotation_total_amount = _quotation_total_amount)	
	AND (_internal_total_amount is null OR internal_total_amount = _internal_total_amount)	
	AND (_in_estimate is null OR in_estimate = _in_estimate)	
	AND (_in_quotation is null OR in_quotation = _in_quotation)	
	AND (_in_internal is null OR in_internal = _in_internal)	
	AND (_id_type is null OR id_type = _id_type)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_quotation_item_count (
	_id	integer DEFAULT null,
	_id_quotation	integer DEFAULT null,
	_id_parent	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_name	character varying(255) DEFAULT null,
	_description	character varying DEFAULT null,
	_remark	character varying DEFAULT null,
	_is_group	boolean DEFAULT null,
	_index	integer DEFAULT null,
	_display_index	character varying(10) DEFAULT null,
	_id_product	integer DEFAULT null,
	_id_service	integer DEFAULT null,
	_id_unit	integer DEFAULT null,
	_id_currency	integer DEFAULT null,
	_estimate_quantity	integer DEFAULT null,
	_quotation_quantity	integer DEFAULT null,
	_internal_quantity	integer DEFAULT null,
	_estimate_unit_cost	numeric DEFAULT null,
	_quotation_unit_cost	numeric DEFAULT null,
	_internal_unit_cost	numeric DEFAULT null,
	_estimate_total_amount	numeric DEFAULT null,
	_quotation_total_amount	numeric DEFAULT null,
	_internal_total_amount	numeric DEFAULT null,
	_in_estimate	boolean DEFAULT null,
	_in_quotation	boolean DEFAULT null,
	_in_internal	boolean DEFAULT null,
	_id_type	integer DEFAULT null,
	_status	integer DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM quotation_item WHERE
	 	(_id is null OR id = _id)	
	AND (_id_quotation is null OR id_quotation = _id_quotation)	
	AND (_id_parent is null OR id_parent = _id_parent)	
	AND (_code is null OR code = _code)	
	AND (_name is null OR name = _name)	
	AND (_description is null OR description = _description)	
	AND (_remark is null OR remark = _remark)	
	AND (_is_group is null OR is_group = _is_group)	
	AND (_index is null OR index = _index)	
	AND (_display_index is null OR display_index = _display_index)	
	AND (_id_product is null OR id_product = _id_product)	
	AND (_id_service is null OR id_service = _id_service)	
	AND (_id_unit is null OR id_unit = _id_unit)	
	AND (_id_currency is null OR id_currency = _id_currency)	
	AND (_estimate_quantity is null OR estimate_quantity = _estimate_quantity)	
	AND (_quotation_quantity is null OR quotation_quantity = _quotation_quantity)	
	AND (_internal_quantity is null OR internal_quantity = _internal_quantity)	
	AND (_estimate_unit_cost is null OR estimate_unit_cost = _estimate_unit_cost)	
	AND (_quotation_unit_cost is null OR quotation_unit_cost = _quotation_unit_cost)	
	AND (_internal_unit_cost is null OR internal_unit_cost = _internal_unit_cost)	
	AND (_estimate_total_amount is null OR estimate_total_amount = _estimate_total_amount)	
	AND (_quotation_total_amount is null OR quotation_total_amount = _quotation_total_amount)	
	AND (_internal_total_amount is null OR internal_total_amount = _internal_total_amount)	
	AND (_in_estimate is null OR in_estimate = _in_estimate)	
	AND (_in_quotation is null OR in_quotation = _in_quotation)	
	AND (_in_internal is null OR in_internal = _in_internal)	
	AND (_id_type is null OR id_type = _id_type)	
	AND (_status is null OR status = _status)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_quotation_item_delete(_id integer)
	RETURNS INT as
$$
BEGIN
		DELETE FROM quotation_item
 		WHERE "id" = _id;
		RETURN 0;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_quotation_item_update 
(	_id integer,
	_id_quotation integer,
	_id_parent integer,
	_code character varying(20),
	_name character varying(255),
	_description character varying,
	_remark character varying,
	_is_group boolean,
	_index integer,
	_display_index character varying(10),
	_id_product integer,
	_id_service integer,
	_id_unit integer,
	_id_currency integer,
	_estimate_quantity integer,
	_quotation_quantity integer,
	_internal_quantity integer,
	_estimate_unit_cost numeric,
	_quotation_unit_cost numeric,
	_internal_unit_cost numeric,
	_estimate_total_amount numeric,
	_quotation_total_amount numeric,
	_internal_total_amount numeric,
	_in_estimate boolean,
	_in_quotation boolean,
	_in_internal boolean,
	_id_type integer,
	_status integer,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE quotation_item 
	SET 		id_quotation = _id_quotation,
		id_parent = _id_parent,
		code = _code,
		name = _name,
		description = _description,
		remark = _remark,
		is_group = _is_group,
		index = _index,
		display_index = _display_index,
		id_product = _id_product,
		id_service = _id_service,
		id_unit = _id_unit,
		id_currency = _id_currency,
		estimate_quantity = _estimate_quantity,
		quotation_quantity = _quotation_quantity,
		internal_quantity = _internal_quantity,
		estimate_unit_cost = _estimate_unit_cost,
		quotation_unit_cost = _quotation_unit_cost,
		internal_unit_cost = _internal_unit_cost,
		estimate_total_amount = _estimate_total_amount,
		quotation_total_amount = _quotation_total_amount,
		internal_total_amount = _internal_total_amount,
		in_estimate = _in_estimate,
		in_quotation = _in_quotation,
		in_internal = _in_internal,
		id_type = _id_type,
		status = _status,
		notification = _notification,
		updated = _now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
